﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Models.Entities;
using Common.Encryption;
using System.Web.Configuration;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace Common.Data
{
    /// <summary>
    /// Author: Mike Barksdale
    /// Generic interface for access to the admin portion of the database.
    /// </summary>
    public interface IAdminDataProvider
    {
        List<ConnectionString> GetConnectionStringsByUser(string userId);
        List<DatabaseType> GetDatabaseTypes();
        ConnectionString GetConnectionString(ConnectionString connectionStringToGet);
        AdminDataProviderResult EditConnectionString(ConnectionString connectionStringToEdit, string originalConnStrName, string originalDatabaseTypeName);
        AdminDataProviderResult AddConnectionString(ConnectionString connectionStringToAdd);
        AdminDataProviderResult DeleteConnectionString(ConnectionString connectionStringToDelete);
        AdminDataProviderResult TestConnectionString(ConnectionString connectionStringToTest);
        AdminDataProviderResult DeleteConnectionStringsByUser(string userId);
    }

    /// <summary>
    /// Author: Mike Barksdale
    /// An enum representing the status of the admin database's activities.
    /// </summary>
    public enum AdminDataProviderStatus
    {
        Success = 0x01,
        Failure = 0x02
    }

    /// <summary>
    /// Author: Mike Barksdale
    /// Simple class that represents the result of activity on the admin database.
    /// </summary>
    public class AdminDataProviderResult
    {
        public AdminDataProviderStatus status { get; set; }
        public string message { get; set; }
    }

    /// <summary>
    /// Author: Mike Barksdale
    /// A class that implements the generic admin database access provider.
    /// </summary>
    public class AdminDataProvider : IAdminDataProvider
    {
        //for encrypting/decrypting information
        private readonly IEncryptionProvider _encryptionProvider;
        
        //connection string to the admin database
        private readonly string _adminConnectionString;

        //constructor
        public AdminDataProvider()
        {
            this._encryptionProvider = new AesEncryptionProvider();
            this._adminConnectionString = WebConfigurationManager.ConnectionStrings["AdminDb"].ConnectionString;
        }

        /// <summary>
        /// Author: Mike Barksdale
        /// Gets the connection strings set up by a given user.
        /// </summary>
        /// <param name="userId">Id of the user for retrieving the connection strings.</param>
        /// <returns>A list of ConnectionString objects. The list is empty if none are found.</returns>
        public List<ConnectionString> GetConnectionStringsByUser(string userId)
        {
            var list = new List<ConnectionString>();

            using (var connection = new OleDbConnection(_adminConnectionString))
            {
                string query = @"select * from ConnectionString where UserId = ?";
                
                var command = new OleDbCommand(query, connection);
                command.Parameters.AddWithValue("UserId", userId.Trim());

                try
                {
                    connection.Open();

                    var reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        list.Add(new ConnectionString
                        {
                            connStrName = reader["ConnStrName"].ToString(),
                            databaseTypeName = reader["DatabaseTypeName"].ToString(),
                            userId = reader["UserId"].ToString(),

                            //decryption start
                            connStrUserName = _encryptionProvider.Decrypt(reader["ConnStrUserName"].ToString(),
                                                  _encryptionProvider.GetPrivateKey()),
                            connStrPassword = _encryptionProvider.Decrypt(reader["ConnStrPassword"].ToString(),
                                                  _encryptionProvider.GetPrivateKey()),
                            connStrServer = _encryptionProvider.Decrypt(reader["ConnStrServer"].ToString(),
                                                  _encryptionProvider.GetPrivateKey()),
                            connStrPort = _encryptionProvider.Decrypt(reader["ConnStrPort"].ToString(),
                                                  _encryptionProvider.GetPrivateKey()),
                            connStrServiceName = _encryptionProvider.Decrypt(reader["ConnStrServiceName"].ToString(),
                                                  _encryptionProvider.GetPrivateKey()),
                            connStrDatabaseName = _encryptionProvider.Decrypt(reader["ConnStrDatabaseName"].ToString(),
                                                  _encryptionProvider.GetPrivateKey()),
                            //decryption end

                            //createDate = reader.GetDateTime(9),
                            //modifiedDate = reader.GetDateTime(10),
                            lastModifiedBy = reader["LastModifiedby"].ToString()

                        });
                    }
                }
                catch (Exception ex)
                {

                }
                finally
                {
                    if (connection.State != System.Data.ConnectionState.Closed)
                        connection.Close();
                }
            }

            return list;
        }

        /// <summary>
        /// Author: Mike Barksdale
        /// Gets a list of the database types.
        /// </summary>
        /// <returns>A list of DatabaseType objects.</returns>
        public List<DatabaseType> GetDatabaseTypes()
        {
            var list = new List<DatabaseType>();

            using (var connection = new OleDbConnection(_adminConnectionString))
            {
                string query = @"select * from DatabaseType";

                var command = new OleDbCommand(query, connection);

                try
                {
                    connection.Open();

                    var reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        list.Add(new DatabaseType
                        {
                            typeName = reader["TypeName"].ToString(),
                            connStrFormat = reader["ConnStrFormat"].ToString()
                        });
                    }
                }
                catch (Exception ex)
                {

                }
                finally
                {
                    if (connection.State != System.Data.ConnectionState.Closed)
                        connection.Close();
                }
            }

            return list;
        }

        /// <summary>
        /// Author: Mike Barksdale
        /// Gets a specific connection string for a user.
        /// </summary>
        /// <param name="connectionStringToGet">Object containing the connection string data to retrieve</param>
        /// <returns>A ConnectionString object</returns>
        public ConnectionString GetConnectionString(ConnectionString connectionStringToGet)
        {
            ConnectionString connectionString = null;

            using (var connection = new OleDbConnection(_adminConnectionString))
            {
                string query = @"select * from ConnectionString where ConnStrName = ? AND DatabaseTypeName = ? AND UserId = ?";

                var command = new OleDbCommand(query, connection);
                command.Parameters.AddWithValue("ConnStrName", connectionStringToGet.connStrName);
                command.Parameters.AddWithValue("DatabaseTypeName", connectionStringToGet.databaseTypeName);
                command.Parameters.AddWithValue("UserId", connectionStringToGet.userId);

                try
                {
                    connection.Open();

                    var reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        connectionString = new ConnectionString
                        {
                            connStrName = reader["ConnStrName"].ToString(),
                            databaseTypeName = reader["DatabaseTypeName"].ToString(),
                            userId = reader["UserId"].ToString(),

                            //decryption start
                            connStrUserName = _encryptionProvider.Decrypt(reader["ConnStrUserName"].ToString(),
                                                  _encryptionProvider.GetPrivateKey()),
                            connStrPassword = _encryptionProvider.Decrypt(reader["ConnStrPassword"].ToString(),
                                                  _encryptionProvider.GetPrivateKey()),
                            connStrServer = _encryptionProvider.Decrypt(reader["ConnStrServer"].ToString(),
                                                  _encryptionProvider.GetPrivateKey()),
                            connStrPort = _encryptionProvider.Decrypt(reader["ConnStrPort"].ToString(),
                                                  _encryptionProvider.GetPrivateKey()),
                            connStrServiceName = _encryptionProvider.Decrypt(reader["ConnStrServiceName"].ToString(),
                                                  _encryptionProvider.GetPrivateKey()),
                            connStrDatabaseName = _encryptionProvider.Decrypt(reader["ConnStrDatabaseName"].ToString(),
                                                  _encryptionProvider.GetPrivateKey()),
                            //decryption end

                            lastModifiedBy = reader["LastModifiedby"].ToString()

                        };
                    }
                }
                catch (Exception ex)
                {

                }
                finally
                {
                    if (connection.State != System.Data.ConnectionState.Closed)
                        connection.Close();
                }
            }

            return connectionString;
        }

        /// <summary>
        /// Author: Mike Barksdale
        /// Tests whether a connection string is valid or not by attempting to connect
        /// to the database it describes.
        /// </summary>
        /// <param name="connectionStringToTest">Object containing the connection string to test</param>
        /// <returns>An AdminDataProviderResult object that indicates whether the test passed or failed</returns>
        public AdminDataProviderResult TestConnectionString(ConnectionString connectionStringToTest)
        {
            var result = new AdminDataProviderResult();
            OleDbConnection connection = null;
            string connectionStringFormat = null;

            try
            {
                //get the format string
                using (connection = new OleDbConnection(_adminConnectionString))
                {
                    string query = "select ConnStrFormat from DatabaseType where TypeName = ?";

                    var command = new OleDbCommand(query, connection);

                    command.Parameters.AddWithValue("TypeName", connectionStringToTest.databaseTypeName);

                    connection.Open();

                    var reader = command.ExecuteReader();

                    if (reader.Read())
                        connectionStringFormat = reader["ConnStrFormat"].ToString();
                    else
                    {
                        result.status = AdminDataProviderStatus.Failure;
                        result.message = "Failed: Unable to find matching connection string format";
                        return result;
                    }
                }

                if (connection != null && connection.State != System.Data.ConnectionState.Closed)
                    connection.Close();

                //format the connection string
                switch (connectionStringToTest.databaseTypeName)
                {
                    case "Oracle":
                        connectionStringFormat = connectionStringFormat.Replace("{ConnStrServer}", connectionStringToTest.connStrServer);
                        connectionStringFormat = connectionStringFormat.Replace("{ConnStrPort}", connectionStringToTest.connStrPort);
                        connectionStringFormat = connectionStringFormat.Replace("{ConnStrServiceName}", connectionStringToTest.connStrServiceName);
                        connectionStringFormat = connectionStringFormat.Replace("{ConnStrUserName}", connectionStringToTest.connStrUserName);
                        connectionStringFormat = connectionStringFormat.Replace("{ConnStrPassword}", connectionStringToTest.connStrServer);
                        break;
                    case "SQL Server":
                        connectionStringFormat = connectionStringFormat.Replace("{ConnStrServer}", connectionStringToTest.connStrServer);
                        connectionStringFormat = connectionStringFormat.Replace("{ConnStrDatabaseName}", connectionStringToTest.connStrDatabaseName);
                        connectionStringFormat = connectionStringFormat.Replace("{ConnStrUserName}", connectionStringToTest.connStrUserName);
                        connectionStringFormat = connectionStringFormat.Replace("{ConnStrPassword}", connectionStringToTest.connStrPassword);
                        break;
                }

                //attempt to connect
                using (connection = new OleDbConnection(connectionStringFormat))
                {
                    connection.Open();
                    connection.Close();

                    result.status = AdminDataProviderStatus.Success;
                    result.message = "Success";
                }
            }
            catch (Exception ex)
            {
                result.status = AdminDataProviderStatus.Failure;
                result.message = "Failed: " + ex.Message;
            }
            finally
            {
                if (connection != null && connection.State != System.Data.ConnectionState.Closed)
                    connection.Close();
            }

            return result;
        }

        /// <summary>
        /// Author: Mike Barksdale
        /// Edits an existing connection string.
        /// </summary>
        /// <param name="connectionStringToEdit">Object containing the connection string to edit</param>
        /// <param name="originalConnStrName">String representing the original connection string's name</param>
        /// <param name="originalDatabaseTypeName">String representing the original connection string's database type</param>
        /// <returns>An AdminDataProviderResult object indicating whether the edit succeeded or not</returns>
        public AdminDataProviderResult EditConnectionString(ConnectionString connectionStringToEdit, string originalConnStrName, string originalDatabaseTypeName)
        {
            var result = new AdminDataProviderResult();

            var originalConnectionString = GetConnectionString(connectionStringToEdit);

            using (var connection = new OleDbConnection(_adminConnectionString))
            {
                string query = @"update ConnectionString
                                 set
                                    ConnStrName = ?, 
                                    DatabaseTypeName = ?, 
                                    ConnStrUserName = ?, 
                                    ConnStrPassword = ?, 
                                    ConnStrServer = ?, 
                                    ConnStrPort = ?,
                                    ConnStrServiceName = ?, 
                                    ConnStrDatabaseName = ?, 
                                    ModifiedDate = ?,
                                    LastModifiedBy = ?
                                 where
                                    ConnStrName = ? AND
                                    DatabaseTypeName = ? AND
                                    UserId = ?";

                var command = new OleDbCommand(query, connection);

                //update start
                command.Parameters.AddWithValue("ConnStrName", connectionStringToEdit.connStrName);
                command.Parameters.AddWithValue("DatabaseTypeName", connectionStringToEdit.databaseTypeName);

                //encryption start
                command.Parameters.AddWithValue("ConnStrUserName", _encryptionProvider.Encrypt(connectionStringToEdit.connStrUserName.Trim(),
                                                                       _encryptionProvider.GetPrivateKey()));
                command.Parameters.AddWithValue("ConnStrPassword", _encryptionProvider.Encrypt(connectionStringToEdit.connStrPassword.Trim(),
                                                                       _encryptionProvider.GetPrivateKey()));
                command.Parameters.AddWithValue("ConnStrServer", _encryptionProvider.Encrypt(connectionStringToEdit.connStrServer.Trim(),
                                                                       _encryptionProvider.GetPrivateKey()));
                command.Parameters.AddWithValue("ConnStrPort", _encryptionProvider.Encrypt(connectionStringToEdit.connStrPort.Trim(),
                                                                       _encryptionProvider.GetPrivateKey()));
                command.Parameters.AddWithValue("ConnStrServiceName", _encryptionProvider.Encrypt(connectionStringToEdit.connStrServiceName.Trim(),
                                                                       _encryptionProvider.GetPrivateKey()));
                command.Parameters.AddWithValue("ConnStrDatabaseName", _encryptionProvider.Encrypt(connectionStringToEdit.connStrDatabaseName.Trim(),
                                                                       _encryptionProvider.GetPrivateKey()));
                //encryption end

                command.Parameters.AddWithValue("ModifiedDate", DateTime.Now);
                command.Parameters.AddWithValue("LastModifiedBy", connectionStringToEdit.lastModifiedBy);
                //update end

                //where clause start
                command.Parameters.AddWithValue("ConnStrName", originalConnStrName);
                command.Parameters.AddWithValue("DatabaseTypeName", originalDatabaseTypeName);
                //command.Parameters.Add("origConnStrName", OleDbType.VarChar, 50, "ConnStrName");
                //command.Parameters.Add("origDatabaseTypeName", OleDbType.VarChar, 30, "DatabaseTypeName");
                command.Parameters.AddWithValue("UserId", connectionStringToEdit.userId);
                //where clause end

                try
                {
                    connection.Open();

                    if (command.ExecuteNonQuery() <= 0)
                    {
                        result.status = AdminDataProviderStatus.Failure;
                        result.message = "Update failed";
                    }
                    else
                    {
                        result.status = AdminDataProviderStatus.Success;
                        result.message = "Success";
                    }
                }
                catch (Exception ex)
                {
                    result.status = AdminDataProviderStatus.Failure;
                    result.message = ex.Message;
                }
                finally
                {
                    if (connection.State != System.Data.ConnectionState.Closed)
                        connection.Close();
                }
            }

            return result;
        }

        /// <summary>
        /// Author: Mike Barksdale
        /// Deletes a connection string.
        /// </summary>
        /// <param name="connectionStringToDelete">An object containing the connection string to delete</param>
        /// <returns>An AdminDataProviderResult object indicating whether the delete succeeded or not</returns>
        public AdminDataProviderResult DeleteConnectionString(ConnectionString connectionStringToDelete)
        {
            var result = new AdminDataProviderResult();

            using (var connection = new OleDbConnection(_adminConnectionString))
            {
                string query = @"delete from ConnectionString 
                                 where ConnStrName = ? AND
                                       DatabaseTypeName = ? AND
                                       UserId = ?";

                var command = new OleDbCommand(query, connection);

                command.Parameters.AddWithValue("ConnStrName", connectionStringToDelete.connStrName);
                command.Parameters.AddWithValue("DatabaseTypeName", connectionStringToDelete.databaseTypeName);
                command.Parameters.AddWithValue("UserId", connectionStringToDelete.userId);

                try
                {
                    connection.Open();

                    if (command.ExecuteNonQuery() <= 0)
                    {
                        result.status = AdminDataProviderStatus.Failure;
                        result.message = "Update failed";
                    }
                    else
                    {
                        result.status = AdminDataProviderStatus.Success;
                        result.message = "Success";
                    }
                }
                catch (Exception ex)
                {
                    result.status = AdminDataProviderStatus.Failure;
                    result.message = ex.Message;
                }
                finally
                {
                    if (connection.State != System.Data.ConnectionState.Closed)
                        connection.Close();
                }
            }

            return result;
        }

        /// <summary>
        /// Author: Mike Barksdale
        /// Deletes all connection strings for a given user id. This is a hard delete.
        /// </summary>
        /// <param name="userId">The user id of the connection strings to delete</param>
        /// <returns>An AdminDataProviderResult object indicating whether or not the operation succeeded</returns>
        public AdminDataProviderResult DeleteConnectionStringsByUser(string userId)
        {
            var result = new AdminDataProviderResult();

            using (var connection = new OleDbConnection(_adminConnectionString))
            {
                string query = @"delete from ConnectionString where UserId = ?";

                var command = new OleDbCommand(query, connection);

                command.Parameters.AddWithValue("UserId", userId);

                try
                {
                    connection.Open();

                    if (command.ExecuteNonQuery() <= 0)
                    {
                        result.status = AdminDataProviderStatus.Failure;
                        result.message = "Delete failed";
                    }
                    else
                    {
                        result.status = AdminDataProviderStatus.Success;
                        result.message = "Success";
                    }
                }
                catch (Exception ex)
                {
                    result.status = AdminDataProviderStatus.Failure;
                    result.message = ex.Message;
                }
                finally
                {
                    if (connection.State != System.Data.ConnectionState.Closed)
                        connection.Close();
                }
            }

            return result;
        }

        /// <summary>
        /// Author: Mike Barksdale
        /// Adds a new connection string.
        /// </summary>
        /// <param name="connectionStringToAdd">An object containing the connection string information to add</param>
        /// <returns>An AdminDataProviderResult object indicating whether the addition succeeded or not</returns>
        public AdminDataProviderResult AddConnectionString(ConnectionString connectionStringToAdd)
        {
            var result = new AdminDataProviderResult();

            using (var connection = new OleDbConnection(_adminConnectionString))
            {
                string query = @"insert into ConnectionString 
                                    (ConnStrName, DatabaseTypeName, UserId, 
                                     ConnStrUserName, ConnStrPassword, ConnStrServer, ConnStrPort,
                                     ConnStrServiceName, ConnStrDatabaseName, CreateDate, ModifiedDate,
                                     LastModifiedBy)
                                 values 
                                    (?,?,?,?,?,?,?,?,?,?,?,?)";

                var command = new OleDbCommand(query, connection);

                command.Parameters.AddWithValue("ConnStrName", connectionStringToAdd.connStrName);
                command.Parameters.AddWithValue("DatabaseTypeName", connectionStringToAdd.databaseTypeName);
                command.Parameters.AddWithValue("UserId", connectionStringToAdd.userId);

                //encryption start
                command.Parameters.AddWithValue("ConnStrUserName", _encryptionProvider.Encrypt(connectionStringToAdd.connStrUserName.Trim(),
                                                                       _encryptionProvider.GetPrivateKey()));
                command.Parameters.AddWithValue("ConnStrPassword", _encryptionProvider.Encrypt(connectionStringToAdd.connStrPassword.Trim(),
                                                                       _encryptionProvider.GetPrivateKey()));
                command.Parameters.AddWithValue("ConnStrServer", _encryptionProvider.Encrypt(connectionStringToAdd.connStrServer.Trim(),
                                                                       _encryptionProvider.GetPrivateKey()));
                command.Parameters.AddWithValue("ConnStrPort", _encryptionProvider.Encrypt(connectionStringToAdd.connStrPort.Trim(),
                                                                       _encryptionProvider.GetPrivateKey()));
                command.Parameters.AddWithValue("ConnStrServiceName", _encryptionProvider.Encrypt(connectionStringToAdd.connStrServiceName.Trim(),
                                                                       _encryptionProvider.GetPrivateKey()));
                command.Parameters.AddWithValue("ConnStrDatabaseName", _encryptionProvider.Encrypt(connectionStringToAdd.connStrDatabaseName.Trim(),
                                                                       _encryptionProvider.GetPrivateKey()));
                //encryption end

                command.Parameters.AddWithValue("CreateDate", connectionStringToAdd.createDate);
                command.Parameters.AddWithValue("ModifiedDate", connectionStringToAdd.modifiedDate);
                command.Parameters.AddWithValue("LastModifiedDate", connectionStringToAdd.lastModifiedBy);

                try
                {
                    connection.Open();

                    if (command.ExecuteNonQuery() <= 0)
                    {
                        result.status = AdminDataProviderStatus.Failure;
                        result.message = "Update failed";
                    }
                    else
                    {
                        result.status = AdminDataProviderStatus.Success;
                        result.message = "Success";
                    }
                }
                catch (Exception ex)
                {
                    result.status = AdminDataProviderStatus.Failure;
                    result.message = ex.Message;
                }
                finally
                {
                    if (connection.State != System.Data.ConnectionState.Closed)
                        connection.Close();
                }
            }

            return result;
        }
    }
}